<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Zone_model extends Model
{
	private $_host;
	private $_dbname;
	private $_username;
	private $_password;
	private $_port;
	function Zone_model()
	{
		parent::Model();
		
		$host = $this->db->hostname;
		$arrHost = explode(':',$host);
		
		$this->_host = trim($arrHost[0]);
		$this->_dbname = $this->db->database;
		$this->_username = $this->db->username;
		$this->_password = $this->db->password;
		$this->_port = $this->db->port;
	}
	
	function get_dashboard($date, $bad_limit = 10)
	{
		$data = array();
		$data['total_active'] = 0;
		$data['total_good'] = 0;
		$data['total_bad'] = 0;
		$data['total_no_report'] = 0;
		$data['total_deleted'] = 0;
				
		mysqli_multi_query($this->db->conn_id, "CALL selfserving_admin_zone_dashboard_v2('$date', '$bad_limit');") or die(show_custom_error(mysqli_error($this->db->conn_id)));
		$result = mysqli_store_result($this->db->conn_id);
		if(mysqli_num_rows($result) > 0)
		{
			$row = mysqli_fetch_array($result);
			$data['total_active'] = $row['num'];
			mysqli_free_result($result);
		}
		
		mysqli_next_result($this->db->conn_id);
		$result = mysqli_store_result($this->db->conn_id);
		if(mysqli_num_rows($result) > 0)
		{
			$row = mysqli_fetch_array($result);
			$data['total_good'] = $row['num'];
			mysqli_free_result($result);
		}
		
		mysqli_next_result($this->db->conn_id);
		$result = mysqli_store_result($this->db->conn_id);
		if(mysqli_num_rows($result) > 0)
		{
			$row = mysqli_fetch_array($result);
			$data['total_bad'] = $row['num'];
			mysqli_free_result($result);
		}
		
		mysqli_next_result($this->db->conn_id);
		$data['total_no_report'] = $data['total_active'] - $data['total_good'] - $data['total_bad'];
		mysqli_next_result($this->db->conn_id);
		$result = mysqli_store_result($this->db->conn_id);
		if(mysqli_num_rows($result) > 0)
		{
			$row = mysqli_fetch_array($result);
			$data['total_deleted'] = $row['num'];
			mysqli_free_result($result);
		}
		
		if(mysqli_more_results($this->db->conn_id))
		{
			mysqli_next_result($this->db->conn_id);
		}
		
		return $data;
	}
	
	function get_all_site()
	{
		$data = array();
		mysqli_multi_query($this->db->conn_id, "CALL selfserving_admin_zone_get_all_site();") or die(show_custom_error(mysqli_error($this->db->conn_id)));
		$result = mysqli_store_result($this->db->conn_id);
		while($row = mysqli_fetch_array($result))
		{
			$data[] = $row;
		}
		mysqli_free_result($result);
		if(mysqli_more_results($this->db->conn_id))
		{
			mysqli_next_result($this->db->conn_id);
		}
		
		return $data;
		
	}
	
	function zone_get_list_zone_info($siteId, $cPage, $numItem, $keyword)
	{
		$data = array();
		$data['lstZone'] = array();
		$data['iCount'] = 0;
		$data['pCount'] = 0;
		$start = ($cPage - 1) * $numItem;
		
		mysqli_multi_query($this->db->conn_id, "CALL selfserving_admin_zone_get_list_paging_v2('$siteId', '$start', '$numItem', '$keyword');")  or die(show_custom_error(mysqli_error($this->db->conn_id)));
		$result = mysqli_store_result($this->db->conn_id);
		while($row = mysqli_fetch_array($result))
		{
			$row['width'] = '';
			$row['height'] = '';
			$x = array();
			$tmp = $row['zonename'];
			$tmp = preg_replace("/^[a-zA-Z]+([0-9]+)?/", "", $tmp);
			$tmp = preg_replace("/^[_|\.\-]+[a-zA-Z]+([0-9]+)?/", "", $tmp);
			$x = preg_split("/[^0-9]/", $tmp);
		 	if(!empty($x))
		 	{
		 		$chkW = false;
		 		$chkH = false;
		 		$num = count($x);
		 		for($i = 0; $i < $num; $i++)
		 		{
		 			if(is_numeric($x[$i]) && $x[$i] > 0)
		 			{
		 				if(!$chkW)
		 				{
		 					$row['width'] = $x[$i];
		 					$chkW = true;
		 				}
		 				else if(!$chkH)
		 				{
		 					$row['height'] = $x[$i];
		 					$chkH = true;
		 				}
		 			}
		 			if($chkW && $chkH)
		 			{
		 				break;
		 			}
		 		}
		 	}
		 	$x = null;
		 	$tmp = null;
			$data['lstZone'][] = $row;
		}
		mysqli_free_result($result);
		if(mysqli_more_results($this->db->conn_id))
		{
			mysqli_next_result($this->db->conn_id);
		}
		
		mysqli_next_result($this->db->conn_id);
        $result = mysqli_store_result($this->db->conn_id);
        $row = mysqli_fetch_array($result);
        $data['iCount'] = $row[0];
        mysqli_free_result($result);
		if(mysqli_more_results($this->db->conn_id))
		{
			mysqli_next_result($this->db->conn_id);
		}
		$data['pCount'] = ($data['iCount'] > $numItem) ? ceil($data['iCount']/$numItem) : 1;
		return $data;
	}
	
	function zone_get_report($strZoneId, $fromdate, $todate)
	{
		$lstData = array();
		$iconn = $this->db->conn_id;
		
		mysqli_multi_query($iconn, "CALL selfserving_admin_zone_get_report_v2('$strZoneId', '$fromdate', '$todate', '0');") or die(show_custom_error(mysqli_error($iconn)));
		$result = mysqli_store_result($iconn);
		while($row = mysqli_fetch_array($result))
		{
			$rc = (int) $row['rc'];
			$v = (int) $row['v'];
			$row['ctr'] = 'N/A';
			$row['ctr'] = ($v == 0 || $v == 'N/A' || $v == '') ? 'N/A' : round(($rc/$v) * 100, 3);
			$lstData[$row['zoneid']] = $row;
		}
		
		mysqli_free_result($result);
		if(mysqli_more_results($iconn))
		{
			mysqli_next_result($iconn);
		}
		return 	$lstData;
	}
	
	/*function get_list_zone($siteId, $typeId, $date, $bad_limit, $cPage, $numItem)
	{
		$data = array();
		$data['lstZone'] = array();
		$data['iCount'] = 0;
		$data['pCount'] = 0;
		$start = ($cPage - 1) * $numItem;
		mysqli_multi_query($this->db->conn_id, "CALL selfserving_admin_zone_get_list_paging('$siteId', '$typeId', '$date', '$bad_limit', '$start', '$numItem');")  or die(show_custom_error(mysqli_error($this->db->conn_id)));
		$result = mysqli_store_result($this->db->conn_id);
		while($row = mysqli_fetch_array($result))
		{
			$row['ctr'] = ($row['ctr'] != '' && $row['ctr'] != 'N/A') ? (float) $row['ctr'] : $row['ctr'];
			$data['lstZone'][] = $row;
		}
		mysqli_free_result($result);
		if(mysqli_more_results($this->db->conn_id))
		{
			mysqli_next_result($this->db->conn_id);
		}
		
		mysqli_next_result($this->db->conn_id);
        $result = mysqli_store_result($this->db->conn_id);
        $row = mysqli_fetch_array($result);
        $data['iCount'] = $row[0];
        mysqli_free_result($result);
		if(mysqli_more_results($this->db->conn_id))
		{
			mysqli_next_result($this->db->conn_id);
		}
		
		$data['pCount'] = ($data['iCount'] > $numItem) ? ceil($data['iCount']/$numItem) : 1;
        
		return $data;
	}*/
	
    function group_category_get_all($langcode, $home_lang)
    {
    	$data = array();
		$iconn = $this->db->conn_id;
   		mysqli_multi_query($iconn, "CALL selfserving_group_category_get_all_v1();") or show_custom_error(mysqli_error($iconn));
		$result = mysqli_store_result($iconn);
		while($row = mysqli_fetch_array($result))
		{
			$tmp = array();
			$tmp['gcat_id'] = $row['categoryid'];
			$tmp['gcat_name'] = ($langcode == 'english') ? $row['categoryname_en'] : $row['categoryname_vi'];
			$tmp['lstSite'] = array();
			$data[] = $tmp;
		}
		$rel = array('2200'=> array(), '1650'=>array(), '1210' => array());
		foreach($rel as $key=>$it)
		{
			$tmp = array();
			foreach($data as $item)
			{
				$tmp[] = $item;
			}
			$rel[$key] = $tmp;
		}
		
			
		mysqli_free_result($result);
		mysqli_next_result($iconn);
        $result = mysqli_store_result($iconn);
		
		$lstSite = array();
		$lstChannel = array();
		while($row = mysqli_fetch_array($result))
		{
			if($row['parentid'] == 0)
			{
				$tmpRow = $row;
				$tmpRow['lstChannel'] = array();
				$lstSite[$row['categoryid']][] = $tmpRow;
			}
			else
			{
				$lstChannel[] = $row;
			}
		}
	
		mysqli_free_result($result);
		if(mysqli_more_results($iconn))
		{
			mysqli_next_result($iconn);
		}
		$numGCat = count($data);
		$numSite = count($lstSite);
		foreach($lstChannel as $channel)
		{
			$numSite = count($lstSite[$channel['categoryid']]);
			for($j = 0; $j < $numSite; $j++)
			{
				if($lstSite[$channel['categoryid']][$j]['siteid'] == $channel['siteid'])
				{
					$lstSite[$channel['categoryid']][$j]['lstChannel'][] = $channel;
					break;
				}
			}
		}
		
		for($i = 0; $i < $numGCat; $i++)
		{
			$numSite = count($lstSite[$data[$i]['gcat_id']]);
			$arrSite = array();
			for($j = 0; $j < $numSite; $j++)
			{
				if(count($lstSite[$data[$i]['gcat_id']][$j]['lstChannel']) == 0)
				{
					$lstSite[$data[$i]['gcat_id']][$j]['lstChannel'][] = $lstSite[$data[$i]['gcat_id']][$j];
				}
			}
			foreach($lstSite[$data[$i]['gcat_id']] as $aItem)
			{
				$arrSite[] = $aItem;
			}
			$data[$i]['lstSite'] = $arrSite;			
		}
		
		for($i = 0; $i < $numGCat; $i++)
		{
			foreach($data[$i]['lstSite'] as $site)
			{
				for($j = 0; $j < $numGCat; $j++)
				{
					if($site['categoryid'] == $rel[$site['price']][$j]['gcat_id'])
					{
						$rel[$site['price']][$j]['lstSite'][] = $site;
						break;
					}
				}
			}
		}
		
		$data = null;
		return $rel;
    }
	
	function get_zone_info_by_list_id($strId)
	{
		$data = array();
		mysqli_multi_query($this->db->conn_id, "CALL selfserving_admin_zone_get_info_by_list_id('$strId');") or die(show_custom_error($this->db->conn_id));
		
		$result = mysqli_store_result($this->db->conn_id);
		while($row = mysqli_fetch_array($result))
		{
			$data[$row['zoneid']] = $row;
		}
		mysqli_free_result($result);
		if(mysqli_more_results($this->db->conn_id))
		{
			mysqli_next_result($this->db->conn_id);
		}
		
		return $data;
		
	}
	
	function get_zone_chart_data($strZoneId, $fromDate, $toDate, $arrZoneId)
	{
		$data = array();
		$dbData = array();
		$numZone = count($arrZoneId);
		//$clickData = array();
		//$viewData = array();
		$lstData = array();
		/*
		for($i = 0; $i < $numZone; $i++)
		{
			$clickData[$arrZoneId[$i]] = array();
			$viewData[$arrZoneId[$i]] = array();
		}
		*/
		mysqli_multi_query($this->db->conn_id, "CALL selfserving_admin_zone_get_report('$strZoneId', '$fromDate', '$toDate');") or die(show_custom_error($this->db->conn_id));
		$result = mysqli_store_result($this->db->conn_id);
		while($row = mysqli_fetch_array($result))
		{
			$rc = (int) $row['rc'];
			$v = (int) $row['v'];
			$row['ctr'] = 'N/A';
			$row['ctr'] = ($v == 0 || $v == 'N/A' || $v == '') ? 'N/A' : round(($rc/$v) * 100, 3);
		 	//$clickData[$row['zoneid']][]=  $rc;
			//$viewData[$row['zoneid']][] = $v;
			$lstData[$row['dt']][$row['zoneid']] = $row;
		}
		
		mysqli_free_result($result);
		if(mysqli_more_results($this->db->conn_id))
		{
			mysqli_next_result($this->db->conn_id);
		}
						
		//$data['click'] = $clickData;
		//$data['view'] = $viewData;
		$data['lstData'] = $lstData;
		return $data;
		
	}
	
	function get_date_range($strDateFrom,$strDateTo, $format = 'd-m-Y') 
    {    
        $aryRange=array();
        $iDateFrom=mktime(0,0,0,substr($strDateFrom,5,2),substr($strDateFrom,8,2),substr($strDateFrom,0,4));
        $iDateTo=mktime(0,0,0,substr($strDateTo,5,2),substr($strDateTo,8,2),substr($strDateTo,0,4));
        if ($iDateTo>=$iDateFrom) 
        {
            array_push($aryRange,date($format, $iDateFrom));
            while ($iDateFrom<$iDateTo) 
            {
                $iDateFrom+=86400;
                array_push($aryRange,date($format,$iDateFrom));
            }
        }
        return $aryRange;
    }
    
    function update_zone_status($zoneid, $status)
    {
    	mysqli_multi_query($this->db->conn_id, "CALL selfserving_admin_zone_update_status('$zoneid', '$status');") or die(show_custom_error($this->db->conn_id));
    	if(mysqli_more_results($this->db->conn_id))
		{
			mysqli_next_result($this->db->conn_id);
		}
    }
    
    function get_list_delete_zone($siteId)
    {
  		$data = array();
		mysqli_multi_query($this->db->conn_id, "CALL selfserving_admin_zone_get_list_delete('$siteId');")  or die(show_custom_error(mysqli_error($this->db->conn_id)));
		$result = mysqli_store_result($this->db->conn_id);
		while($row = mysqli_fetch_array($result))
		{
			$data[] = $row;
		}
		mysqli_free_result($result);
		if(mysqli_more_results($this->db->conn_id))
		{
			mysqli_next_result($this->db->conn_id);
		}
		
		return $data;
    }
    
	function zone_get_zone_info_by_list_id($strId)
	{
		$data = array();
		$iconn = $this->db->conn_id;
		mysqli_multi_query($iconn, "CALL selfserving_admin_zone_get_info_by_list_id('$strId');") or die(show_custom_error(mysqli_error($iconn)));
		
		$result = mysqli_store_result($iconn);
		while($row = mysqli_fetch_array($result))
		{
			$data[$row['zoneid']] = $row;
		}
		mysqli_free_result($result);
		if(mysqli_more_results($iconn))
		{
			mysqli_next_result($iconn);
		}
		
		return $data;
		
	}
	
	function zone_get_zone_chart_data($strZoneId, $fromDate, $toDate, $arrZoneId)
	{
		$data = array();
		$dbData = array();
		$numZone = count($arrZoneId);
		//$clickData = array();
		//$viewData = array();
		$lstData = array();
		/*
		for($i = 0; $i < $numZone; $i++)
		{
			$clickData[$arrZoneId[$i]] = array();
			$viewData[$arrZoneId[$i]] = array();
		}
		*/
		$iconn = $this->db->conn_id;
		mysqli_multi_query($iconn, "CALL selfserving_admin_zone_get_report('$strZoneId', '$fromDate', '$toDate');") or die(show_custom_error(mysqli_error($iconn)));
		$result = mysqli_store_result($iconn);
		while($row = mysqli_fetch_array($result))
		{
			$rc = (int) $row['rc'];
			$v = (int) $row['v'];
			$row['ctr'] = 'N/A';
			$row['ctr'] = ($v == 0 || $v == 'N/A' || $v == '') ? 'N/A' : round(($rc/$v) * 100, 3);
		 	//$clickData[$row['zoneid']][]=  $rc;
			//$viewData[$row['zoneid']][] = $v;
			$lstData[$row['dt']][$row['zoneid']] = $row;
		}
		
		mysqli_free_result($result);
		if(mysqli_more_results($iconn))
		{
			mysqli_next_result($iconn);
		}
						
		//$data['click'] = $clickData;
		//$data['view'] = $viewData;
		$data['lstData'] = $lstData;
		return $data;
		
	}
	
	function zone_get_date_range($strDateFrom,$strDateTo, $format = 'd-m-Y') 
    {    
        $aryRange=array();
        $iDateFrom=mktime(0,0,0,substr($strDateFrom,5,2),substr($strDateFrom,8,2),substr($strDateFrom,0,4));
        $iDateTo=mktime(0,0,0,substr($strDateTo,5,2),substr($strDateTo,8,2),substr($strDateTo,0,4));
        if ($iDateTo>=$iDateFrom) 
        {
            array_push($aryRange,date($format, $iDateFrom));
            while ($iDateFrom<$iDateTo) 
            {
                $iDateFrom+=86400;
                array_push($aryRange,date($format,$iDateFrom));
            }
        }
        return $aryRange;
    }
    
    function zone_get_list_delete_zone($siteId, $cPage, $numItem)
    {
  		$data = array();
  		$start = ($cPage - 1) * $numItem;
  		$iconn = $this->db->conn_id;
		mysqli_multi_query($iconn, "CALL selfserving_admin_zone_get_list_delete('$siteId', '$start', '$numItem');")  or die(show_custom_error(mysqli_error($iconn)));
		$result = mysqli_store_result($iconn);
		$data['lstZone'] = array();
		$data['iCount'] = 0;
		$data['pCount'] = 0;
		while($row = mysqli_fetch_array($result))
		{
			$row['width'] = '';
			$row['height'] = '';
			$x = array();
			$tmp = $row['zonename'];
			$tmp = preg_replace("/^[a-zA-Z]+([0-9]+)?/", "", $tmp);
			$tmp = preg_replace("/^[_|\.\-]+[a-zA-Z]+([0-9]+)?/", "", $tmp);
			$x = preg_split("/[^0-9]/", $tmp);
		 	if(!empty($x))
		 	{
		 		$chkW = false;
		 		$chkH = false;
		 		$num = count($x);
		 		for($i = 0; $i < $num; $i++)
		 		{
		 			if(is_numeric($x[$i]) && $x[$i] > 0)
		 			{
		 				if(!$chkW)
		 				{
		 					$row['width'] = $x[$i];
		 					$chkW = true;
		 				}
		 				else if(!$chkH)
		 				{
		 					$row['height'] = $x[$i];
		 					$chkH = true;
		 				}
		 			}
		 			if($chkW && $chkH)
		 			{
		 				break;
		 			}
		 		}
		 	}
		 	$x = null;
		 	$tmp = null;
			$data['lstZone'][] = $row;
		}
		mysqli_free_result($result);
		if(mysqli_more_results($this->db->conn_id))
		{
			mysqli_next_result($this->db->conn_id);
		}
		
		mysqli_next_result($this->db->conn_id);
        $result = mysqli_store_result($this->db->conn_id);
        $row = mysqli_fetch_array($result);
        $data['iCount'] = $row[0];
        mysqli_free_result($result);
		if(mysqli_more_results($this->db->conn_id))
		{
			mysqli_next_result($this->db->conn_id);
		}
		$data['pCount'] = ($data['iCount'] > $numItem) ? ceil($data['iCount']/$numItem) : 1;
		return $data;
    }
	
 }